package br.com.fiap.trabalho.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCAuxiliarDAO {

	private Connection cn;
	private PreparedStatement ps;
	private ResultSet rs;
	
	private boolean abreConexao() throws Exception{
		try{
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); 
			String url = "jdbc:derby:simpleDB;create=true";
			cn = DriverManager.getConnection(url,"TrabalhoPersistenciaJava","TrabalhoPersistenciaJava");
			return true;
		}
		catch(Exception ex){
			throw ex;
		}
	}
	
	public int selecionaMaxCategory() {
		int valor = 0;
		
		try {
			abreConexao();

			ps = cn.prepareStatement("SELECT MAX(ID_CATEGORY) FROM CATEGORY ");
			rs = ps.executeQuery();
			
			while (rs.next()) {
				valor = rs.getInt(1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		finally{
			try {
				cn.setAutoCommit(true);
				ps.close();
				cn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return valor;
	}
	
	public int selecionaMaxStudio() {
		int valor = 0;
		
		try {
			abreConexao();

			ps = cn.prepareStatement("SELECT MAX(ID_STUDIO) FROM STUDIO ");
			rs = ps.executeQuery();
			
			while (rs.next()) {
				valor = rs.getInt(1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		finally{
			try {
				cn.setAutoCommit(true);
				ps.close();
				cn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return valor;
	}
	
	public int selecionaMaxActor() {
		int valor = 0;
		
		try {
			abreConexao();

			ps = cn.prepareStatement("SELECT MAX(ID_ACTOR) FROM ACTOR ");
			rs = ps.executeQuery();
			
			while (rs.next()) {
				valor = rs.getInt(1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		finally{
			try {
				cn.setAutoCommit(true);
				ps.close();
				cn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return valor;
	}
	
	public int selecionaMaxMovie() {
		int valor = 0;
		
		try {
			abreConexao();

			ps = cn.prepareStatement("SELECT MAX(ID_MOVIE) FROM MOVIE ");
			rs = ps.executeQuery();
			
			while (rs.next()) {
				valor = rs.getInt(1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		finally{
			try {
				cn.setAutoCommit(true);
				ps.close();
				cn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return valor;
	}
	
	public int selecionaCountCategory() {
		int valor = 0;
		
		try {
			abreConexao();

			ps = cn.prepareStatement("SELECT COUNT(ID_CATEGORY) FROM CATEGORY ");
			rs = ps.executeQuery();
			
			while (rs.next()) {
				valor = rs.getInt(1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		finally{
			try {
				cn.setAutoCommit(true);
				ps.close();
				cn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return valor;
	}
}
